% Copyright © 2019-2024 
% Юдинцев В. В. 
\documentclass[12pt]{beamer} 
%\usepackage{beamerthemesplit}

\usepackage{fontspec}
\setsansfont{PT Sans}
\setmonofont{PT Mono}

\usepackage{beamerthemesplit}
\usefonttheme[onlymath]{serif}

\usetheme{CambridgeUS}
\usecolortheme{lily} 

\hypersetup{unicode=true}
\DeclareFontEncoding{T1}
\fontencoding{T1}
\usepackage[russian]{babel}

\usepackage{amsmath,amssymb,amsthm}
\usepackage{graphics,graphicx}
\usepackage{listings}
%\usepackage{media9} 
\usepackage{fancyvrb} 
\usepackage{booktabs} 
\usepackage{colortbl}
\usepackage{multirow}
\usepackage{verbatim}
%\usepackage{fancyvrb}
\usepackage{color}
\usepackage{url}
\usepackage{qrcode}
\usepackage{pifont}% http://ctan.org/pkg/pifont

\definecolor{mygreen}{RGB}{28,172,0} % color values Red, Green, Blue
\definecolor{mylilas}{RGB}{170,55,241}
\definecolor{codecolor}{RGB}{0,120,0}
\definecolor{light-gray}{gray}{0.9}
\definecolor{light-green}{RGB}{205,255,204}
\definecolor{light-red}{rgb}{0.9, 0.8, 0.8}
\definecolor{light-yellow}{rgb}{1,1,0.85}
\definecolor{light-blue}{RGB}{255,204,153}
\definecolor{dark-blue}{rgb}{0,0,0.6}
\definecolor{dark-red}{RGB}{204,0,0}
\definecolor{dark-green}{RGB}{28,172,0}

\newcommand{\cmark}{\textcolor{green}{\ding{51}}}
\newcommand{\xmark}{\textcolor{red}{\ding{55}}}

\setbeamercolor{frametitle}{fg=dark-red,bg=gray!10}
\setbeamerfont{title}{series=\bfseries,parent=structure}
\setbeamerfont{frametitle}{series=\bfseries,parent=structure}
\setbeamertemplate{blocks}[rounded][shadow=false]
\setbeamercolor{block title}{fg=structure,bg=white}
\setbeamertemplate{navigation symbols}{}

\renewcommand{\emph}[1]{\textcolor{dark-blue}{#1}}
\newcommand{\emphb}[1]{\textcolor{dark-blue}{\textbf{#1}}}

\usepackage{tikz}
\usetikzlibrary{er,arrows,matrix,positioning,shapes,snakes}

%\warn{\matcode}[1]{ \textcolor{codecolor}{\texttt{#1}}}
%\newcommand{\matcode}[1]{\begin{Verbatim}[formatcom=\color{red}]#1\end{Verbatim}}
\definecolor{codecolor}{RGB}{0,150,0}
\newcommand{\matcode}[1]{ \textcolor{codecolor}{\texttt{#1}}}


\title[Базы данных / Основы SQL / DDL]{Основы SQL. DDL}
\subtitle{Базы данных}
\author[Кафедра ММЭ]{Юдинцев В. В.}
\institute[Самарский университет]{Кафедра математических методов в экономике}

%\pgfdeclareimage[height=1cm]{logo}{ssau.pdf}
%\logo{\pgfuseimage{logo}}
%\setbeameroption{show notes}
%\graphicspath{{img/}}

\lstset{language=SQL,  
    keepspaces=true,
    extendedchars=true,
    basicstyle={\footnotesize  \color{black}},
    breaklines=true,    
    keywordstyle=\color{blue},
    morekeywords=[2]{1}, keywordstyle=[2]{\color{codecolor}},
    identifierstyle={ \bf \color{black} },
    stringstyle=\color{mylilas},
    commentstyle=\color{mygreen},%
    showstringspaces=false,
    numbers=left,
    numberstyle={\tiny \color{gray}},
    numbersep=4pt,     
    frame=single
}

\makeatother
\setbeamertemplate{footline}
{
  \leavevmode%
  \hbox{%  
  \begin{beamercolorbox}[wd=.3\paperwidth,ht=2.25ex,dp=1ex,center]{author in head/foot}%
    \usebeamerfont{author in head/foot}\insertshortauthor
  \end{beamercolorbox}%  
  \begin{beamercolorbox}[wd=.6\paperwidth,ht=2.25ex,dp=1ex,center]{title in head/foot}%
    \usebeamerfont{title in head/foot}\insertshorttitle
  \end{beamercolorbox}%
  \begin{beamercolorbox}[wd=.1\paperwidth,ht=2.25ex,dp=1ex,center]{date in head/foot}%
%    \insertframenumber{} / \inserttotalframenumber\hspace*{1ex}
     \insertframenumber{} 
  \end{beamercolorbox}}%
  \vskip0pt%
}

\setbeamertemplate{headline}{}
%\setbeamertemplate{footline}{1}

\AtBeginSection[]{ 
  \begin{frame}[plain]
  %\vfill  
  \begin{tikzpicture}
  \useasboundingbox (0,0) rectangle(\the\paperwidth,\the\paperheight);  
  \fill[color=dark-red]   (-1cm, 3.9cm) rectangle(\the\paperwidth, 5.9cm);
 %\usebeamerfont{title}\insertsectionhead\par%
  %\node[text width=\the\paperwidth,align=center] at (current page.center) {\color{ExecusharesWhite}\Large\textbf{\insertsectionhead}};  
\node[text width=\the\paperwidth,align=center] at (6cm,4.9cm) {\color{white}\Large\textbf{\insertsectionhead}};
  \end{tikzpicture}
  \end{frame}
}

\newcommand{\imgsrc}[1]{ 
\begin{tikzpicture}[remember picture,overlay]
  \node[anchor=north, rotate=90, inner sep=0.5pt] at (current page.west) {\fontsize{5}{6}\selectfont \textcolor{light-gray}{#1}};
\end{tikzpicture}  
}

% 
% Документ
%
%
\begin{document}

{
\setbeamercolor{background canvas}{bg=white} 
\usebackgroundtemplate{\includegraphics[width=\paperwidth,height=\paperheight]{back.png}}
\begin{frame}[plain]
\maketitle
\end{frame}
}

\frame{
\frametitle{Содержание}
\tableofcontents
}

\section{SQL}

\begin{frame}
  \frametitle{Язык SQL}  
  \begin{itemize}
    \item \emphb{SQL} -- \emph{язык структурированных запросов} (Structured Query Language) -- специальный язык программирования, разработанный для управления данными в реляционных системах управления базами данных.      
    \item Cоздан в 1970-х годах под названием SEQUEL для СУБД System R. 
    \item Первый официальный стандарт языка был принят в 1986 году
    \item Развитие: SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003, SQL:2006, SQL:2008, SQL:2011, SQL:2016. 
  \end{itemize}  
\end{frame}

\begin{frame}
\frametitle{Язык SQL}
\begin{columns}
  \column{0.5\linewidth}
  \includegraphics[width=0.9\linewidth]{SQL-lines.png}
  \column{0.5\linewidth}
  Язык \emphb{SQL} \emph{предназначен} для манипулирования данными в реляционных базах данных, определения структуры баз данных и для управления правами доступа к данным в многопользовательской среде.
\end{columns}
\imgsrc{Изо: \url{https://info-comp.ru/obucheniest/747-subqueries-in-t-sql.html}}
\end{frame}

\begin{frame}
  \frametitle{Синтаксис}
  Как и во всяком языке программирования, в SQL есть алфавит (допустимые символы), ключевые слова, поддерживаемые типы данных, встроенные константы и функции, операторы:  
  \begin{itemize}    
    \item SELECT, INSERT, UPDATE, DELETE, DROP, ...
    \item точные числовые типы, приближенные числовые типы, типы даты и времени, строковые типы  
    \item сложение, вычитание, умножение, деление, остаток от деления
    \item операторы сравнения, логические операторы
    \item CAST, TRIM, LOWER, UPPER
  \end{itemize}
\end{frame}

\begin{frame}
  \frametitle{Основные типы операторов SQL}
  \begin{itemize}
    \item \emph{Язык определения данных} (Data Definition Language -- DDL): операторы создания и управления структурами базы данных
    \item \emph{Язык манипулирования данными} (DML): операторы извлечения, вставки, обновления и удаления данных в БД
    \item \emph{Язык определения доступа к данным} Data Control Language (DCL): операторы определения доступа к данным.    
    \item \emph{Язык управления транзакциями (TCL)}: используется для контроля обработки транзакций в БД.
  \end{itemize}
\end{frame}

\section{Создание базы данных}

\begin{frame}[fragile]
\frametitle{Создание базы данных}
\begin{itemize}
\item В системах клиент-сервер, таких как MySQL, для создания базы данных используется команда \emph{CREATE DATABASE}.
\item В однопользовательской системе SQLite база создается автоматически, если файла БД, указанного в параметрах подключения, не существует.
\item \emph{Для выполнения лабораторных работ по этому курсу для каждой учетной записи уже создана БД}.
\end{itemize}
\end{frame}

\begin{frame}[fragile]
  \frametitle{Создание базы данных}
  \begin{itemize}
  \item Для подключения к серверу СУБД необходимо установить приложение-клиент, например   
  \begin{itemize}
    \item \emphb{DBeaver} (рекомендуется) \\ \url{https://dbeaver.io}
    \item \emphb{HeidiSQL} \\ \url{https://www.heidisql.com}
    \item \emphb{MySQL Workbench} \\ \url{https://www.mysql.com/products/workbench/}
    \item \emphb{MySQL Shell} (приложение командной строки) \\
    \url{http://dev.mysql.com/downloads/shell/}
  \end{itemize}
  \end{itemize}
  \end{frame}
  

\begin{frame}[fragile]
\frametitle{Подключение из командной строки}
\begin{lstlisting}
localhost:> mysql -u student -p -h 192.168.0.11 
\end{lstlisting}
Опции команды \emph{mysql}
\begin{itemize}
  \item \emphb{-u} имя пользователя
  \item \emphb{-p} запросить пароль
  \item \emphb{-h} адрес сервера
\end{itemize}
\includegraphics[width=1\linewidth]{connect_mysql.png}
\end{frame}

\begin{frame}[fragile]
\frametitle{Подключение из командной строки}
После подлючения к СУБД можно создать БД (если есть права на это): 
\begin{lstlisting}
mysql> create database db1;
\end{lstlisting}
или подключиться к существующей базе данных:
\begin{lstlisting}
mysql> use db1;
\end{lstlisting}
или посмотреть список существующих баз данных:
\begin{lstlisting}
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
\end{lstlisting}
\end{frame}
  

\begin{frame}[fragile]
\frametitle{Подключение из среды Python}
\begin{lstlisting}
import mysql.connector
  
mydb = mysql.connector.connect(
    host="192.168.0.11",
    user="python",
    passwd="n9jguqLc5e",
    database="mydatabase"
    )
\end{lstlisting}
В результате выполнения этого кода создается объект (mydb) -- подключение к БД.
\end{frame}

\begin{frame}
\frametitle{Графический клиент DBEAVER}
DBeaver — платформенно-независимый клиент баз данных, написанный на Java: \url{https://dbeaver.io}.
\begin{center}
\includegraphics[width=0.9\linewidth]{dbeaver1.png}
\end{center}
\end{frame}

\begin{frame}
  \frametitle{Графический клиент DBEAVER}
  \begin{center}
  \includegraphics[width=1\linewidth]{dbeaver2.png}
  \end{center}
  \end{frame}

% \begin{frame} 
%   \frametitle{Графический клиент DBEAVER}
%   \begin{center}
%   \includegraphics[width=1\linewidth]{dbeaver3.png}
%   \end{center}
% \end{frame}

\begin{frame}
  \frametitle{Графический клиент DBEAVER}
  \begin{center}
  \includegraphics[width=1\linewidth]{dbeaver4.png}
  \end{center}
\end{frame}


\section{Создание таблиц}

\begin{frame}[fragile]
\frametitle{Создание таблицы}
Создание простой таблицы \emphb{имя} с тремя столбцами
\begin{lstlisting}
CREATE TABLE имя (
    столбец1 тип1,
    столбец2 тип2,
    столбец3 тип3
);
\end{lstlisting}  
Таким образом, при создании таблицы необходимо обязательно указать 
\begin{itemize}
  \item \emphb{имя} таблицы
  \item \emphb{наименования столбцов}
  \item \emphb{тип} данных в каждом столбце
\end{itemize}
\end{frame}


\begin{frame}[fragile]
\frametitle{Пример определения таблицы Students}
\begin{lstlisting}
CREATE TABLE Students (
    Student_ID INTEGER,
    FNAME      VARCHAR(50),
    LNAME      VARCHAR(50),
    MNAME      VARCHAR(50)
);
\end{lstlisting}  
\begin{itemize}
  \item \emphb{Student\_ID} - номер студента (целое число)
  \item \emphb{FNAME} - имя (до 50 символов)
  \item \emphb{LNAME} - фамилия (до 50 символов)
  \item \emphb{MNAME} - отчество (до 50 символов)
\end{itemize}
\end{frame}
  

\begin{frame}
  \frametitle{Основные \emphb{строковые} типы}  
  \begin{itemize}
    \item \emphb{CHAR(n)} \\
    Строка \emph{фиксированной} длины n. 
    \item \emphb{VARCHAR(n)} \\
    Строка \emph{переменной} длины, но не более n символов.
    \item \emphb{TEXT} \\
    Текст не более 65 535 символов.
  \end{itemize}  
\end{frame}

\begin{frame}
  \frametitle{Основные \emphb{числовые} типы}
  Целые числа (стандарт SQL не определяет размер этих чисел):
  \begin{itemize}
    \item \emphb{SMALLINT}
    \item \emphb{INTEGER}
    \item \emphb{BIGINT}        
  \end{itemize}  
  Вещественные числа 
  \begin{itemize}    
    \item \emphb{DECIMAL(p,s)} \\ $p$ -- общее количество цифр,\\ $s$ -- количество цифр после запятой. 
    \item \emphb{REAL} 
    \item \emphb{FLOAT} 
  \end{itemize}  
\end{frame}

\begin{frame}
  \frametitle{Дата и время}  
  \begin{itemize}
    \item \emphb{DATE} (день, месяц и год)
    \item \emphb{TIME} (часы, минуты и секунды)
    \item \emphb{TIMESTAMP} (день, месяц, год, часы, минуты и секунды)           
  \end{itemize}  
\end{frame}

\subsection*{MySQL}

\begin{frame}
  \frametitle{Некоторые \emphb{числовые типы MySQL}}
  \begin{itemize}
    \item \emphb{TINYINT} \\
    1 байт, от -128 до 127
    \item \emphb{SMALLINT} \\
    2 байта, от -32768 до 32767
    \item \emphb{INT} \\
    4 байта, от -2147483648 до 2147483647
    \item \emphb{BIGINT}  \\   
    8 байт BIGINT, \\ от -9223372036854775808 до 9223372036854775807
    \item \emphb{SERIAL}  псевдоним для \\
    \emph{BIGINT UNSIGNED NOT NULL AUTO\_INCREMENT UNIQUE}
  \end{itemize}  
\end{frame}

\begin{frame}
  \frametitle{Некоторые \emphb{числовые типы MySQL}}
  \begin{itemize}
    \item \emphb{FLOAT}  \\   
    4 байта, от $-3.4...\cdot 10^{38}$ до $3.4...\cdot 10^{38}$
    \item \emphb{DOUBLE}  \\   
    8 байта, от $-1.79...\cdot 10^{308}$ до $1.79...\cdot 10^{308}$
    \item \emphb{REAL} синоним \emphb{DOUBLE}    
    \item \emphb{DECIMAL(M,D)} \\
    max(M) = 65 (по умолчанию: 10) \\
    max(D) = 30 (по умолчанию: 0) \\
    \emphb{DECIMAL(5,2)} : -999.99 до 999.99
  \end{itemize}  
\end{frame}

\begin{frame}
  \frametitle{Типы даты и времени MySQL}
  \begin{itemize}
    \item \emphb{DATE} (дата) \\
    YYYY.MM.DD: от 1000-01-01 до 9999-12-31
    \item \emphb{DATETIME} (дата и время) \\
    YYYY.MM.DD HH.MM.SS: \\ 
    от 1000-01-01 00:00:00 to 9999-12-31 23:59:59
    \item \emphb{TIMESTAMP} \\ 
    от 1970-01-01 00:00:01 UTC до 2038-01-09 03:14:07 UTC \\ 
    хранится как количество секунд с 1970-01-01 00:00:00 UTC
  \end{itemize}  
\end{frame}


\begin{frame}[fragile]
\frametitle{Пример таблицы}
В Самарском университете идентификатором студента может быть номер зачетной книжки, который представляет собой строку вида \emph{2017-01011}. В это случае определение таблицы для хранения информации о студентах может иметь вид:
\begin{lstlisting}
CREATE TABLE Students (
   Student_ID CHAR(10),
   FNAME      VARCHAR(50),
   LNAME      VARCHAR(50),
   MNAME      VARCHAR(50)
);
\end{lstlisting}  
\end{frame}

\begin{frame}[fragile]
\frametitle{ENUM -- перечисляемые значения}
\begin{lstlisting}  
CREATE TABLE buyers (
  email VARCHAR(25) NOT NULL, 
  fname VARCHAR(25) NOT NULL, 
  lname VARCHAR(25) NOT NULL, 
  sex ENUM ('F','M')
);
\end{lstlisting}  
Поле \emphb{sex} (тип -- строка) принимает два возможных значения \emphb{M}ale или \emphb{F}emale, а также может иметь значение \emph{NULL}. 
\end{frame}

\section{Обеспечение целостности данных}

\subsection*{PRIMARY KEY}

\begin{frame}[c]
  \frametitle{Первичный ключ}
  \begin{itemize}
    \item \emphb{Первичный ключ} -- набор атрибутов, уникально идентифицирующий запись в таблице. 
    \item Если первичный ключ состоит из единственного атрибута, его называют \emphb{простым ключом}.
    \item Если первичный ключ состоит из двух и более атрибутов, его называют \emphb{составным ключом}.
  \end{itemize}
\end{frame}

\begin{frame}
\frametitle{PRIMARY KEY -- Первичный ключ}
\begin{center}
\includegraphics[width=0.9\linewidth]{connor.png}
\end{center}
\end{frame}


\begin{frame}[fragile]
\frametitle{PRIMARY KEY -- Первичный ключ}
В таблице \emph{buyers} поле \emph{email} является первичным ключом:
\begin{lstlisting}
CREATE TABLE buyers (
  email VARCHAR(25) NOT NULL PRIMARY KEY, 
  fname VARCHAR(25),
  lname VARCHAR(25) NOT NULL
);
\end{lstlisting}
\begin{itemize}
  \item \emphb{PRIMARY KEY} -- поле \emph{email} является ключевым и в таблице \alert{не может быть двух строк с одинаковым значением} этого \alert{поля}
  \item \emphb{NOT NULL} -- поле \emph{email} не может быть пустым
\end{itemize}

\end{frame}

\begin{frame}[fragile]
\frametitle{PRIMARY KEY -- Первичный ключ}
Первичный ключ может быть объявлен при помощи ключевого слова \emphb{CONSTRAINT}:
\begin{lstlisting}
CREATE TABLE buyers (
    email VARCHAR(25) NOT NULL, 
    fname VARCHAR(25),
    lname VARCHAR(25) NOT NULL,
    CONSTRAINT PK_buyer PRIMARY KEY (email, lname)
);
\end{lstlisting}
Таким способом может быть объявлен \emph{составной первичный ключ}. В этом примере "покупатели"  идентифицируются (различаются) по адресу электронной почты И фамилии.  
\end{frame}
  
\subsection*{FOREGIN KEY}

\begin{frame}
\frametitle{FOREGIN KEY -- Внешний ключ}
\begin{itemize}
  \item \alert{FOREIGN KEY} (в переводе с английского языка - <<внешний ключ>>) - это ограничение целостности базы данных, которое используется для связи двух таблиц по значению одного или нескольких полей. 
  \item В таблице, которая является <<дочерней>> в отношении другой <<родительской>> таблицы, столбец \alert{FOREIGN KEY} ссылается на \alert{PRIMARY KEY} или \alert{UNIQUE KEY} в "родительской" таблице. 
  \item Это позволяет обеспечить целостность данных и поддерживать связь между таблицами. 
  \item Если запись в <<родительской>> таблице удаляется или изменяется, то \alert{FOREIGN KEY} гарантирует, что связанные записи в <<дочерней>> таблице также будут удалены или изменены.
\end{itemize}
\end{frame}


\begin{frame}
  \frametitle{FOREGIN KEY -- Внешний ключ}
  Сущность \emph{товарный чек} ссылается на сущность \emph{Покупатель}. 
  \begin{center}
  \includegraphics[width=0.8\linewidth]{Чек-Покупатель.png}
  \end{center}
  Отношение между сущностями \emph{Покупатель} -- \emph{Товарный чек} \emphb{один ко многим}: у товарного чека может быть только один плательщик (покупатель), у покупателя может быть несколько товарных чеков.
\end{frame}

\begin{frame}[fragile]
  \frametitle{Таблица \emph{ПОКУПАТЕЛЬ}}
  Главным ключом таблицы \emph{Покупатель} является адрес \emph{электронной почты}.
  \begin{center}
    \includegraphics[width=0.8\linewidth]{Чек-Покупатель.png}
    \end{center}  
  \begin{lstlisting}
CREATE TABLE buyers (
  email VARCHAR(25) NOT NULL, 
  fname VARCHAR(25), 
  lname VARCHAR(25) NOT NULL, 
  PRIMARY KEY(email)
);
\end{lstlisting}
\end{frame}

\begin{frame}[fragile]
  \frametitle{Таблица \emph{ТОВАРНЫЙ ЧЕК}}
  \begin{center}
    \includegraphics[width=0.8\linewidth]{Чек-Покупатель.png}
  \end{center}  
\begin{lstlisting}
CREATE TABLE sales_slip (
  id INTEGER NOT NULL, 
  sale_date DATE, 
  buyer_email VARCHAR(25) NOT NULL, 
PRIMARY KEY PK_sales_slip (id, sale_date),  
FOREIGN KEY (buyer_email) REFERENCES buyers (email)
);
\end{lstlisting}
\end{frame}

\begin{frame}[fragile]
\frametitle{FOREGIN KEY -- Внешний ключ}
\begin{lstlisting}
  CREATE TABLE sales_slip (
    id INTEGER NOT NULL, 
    sale_date DATE, 
    buyer_email VARCHAR(25) NOT NULL, 
  PRIMARY KEY PK_sales_slip (id, sale_date),  
  FOREIGN KEY (buyer_email) REFERENCES buyers (email)
  );
\end{lstlisting}
\begin{itemize}
  \item Поле \emph{buyer\_email} ссылается на поле \emph{email} таблицы \emph{buyers}.
  \item Это поле не может быть пустым (NULL): у каждого чека должен быть хотя бы один плательщик.
  \item В таблицу \emphb{sales\_slip} невозможно внести информацию о счёте без ссылки на покупателя из таблицы \emphb{buyers}.
\end{itemize}  
\end{frame}


\begin{frame}
\frametitle{Естественный и суррогатный ключ}
\begin{itemize}
  \item \emphb{Суррогатный ключ} -- это дополнительное поле, добавленное к уже имеющимся полям таблицы, которое служит первичным ключом. Значение этого поля \emph{не образуется на основе каких-либо других данных из БД}, а \alert{генерируется искусственно}
  \item Cуррогатный ключ — это обычно числовое поле, в которое заносятся значения из возрастающей числовой последовательности 
\end{itemize}
\end{frame}

\begin{frame}
\frametitle{Естественный ключ}
\includegraphics[width=1\linewidth]{surrogate1.png} 
\end{frame}

\begin{frame}[fragile]
\frametitle{Естественный ключ}
\begin{lstlisting}
CREATE TABLE persons (
 fname VARCHAR(50) NOT NULL,
 lname VARCHAR(50) NOT NULL, 
 birthdate DATE NOT NULL, 
 address VARCHAR(100) NOT NULL,
CONSTRAINT PK_persons PRIMARY KEY PK_PERSONS (fname, lname, birthdate)    
);
\end{lstlisting}
У это таблицы составной первичный ключ, что делает громоздким организацию связей с другими таблицами.
\end{frame}

\begin{frame}[fragile]
\frametitle{Естественный ключ}
\begin{lstlisting}
CREATE TABLE payments (
 payment_id   INTEGER NOT NULL,
 payment_date DATE NOT NULL,
 payer_fname VARCHAR(50) NOT NULL,
 payer_lname VARCHAR(50) NOT NULL, 
 payer_birthdate DATE NOT NULL,
CONSTRAINT PK_payments 
 PRIMARY KEY PK_PAYMENTS (payment_id, payment_date),
CONSTRAINT FK_payments 
 FOREIGN KEY (payer_fname,payer_lname,payer_birthdate)
 REFERENCES persons(fname,lname,birthdate)
);
\end{lstlisting}
\end{frame}

\begin{frame}
\frametitle{Суррогатный ключ}
\includegraphics[width=1\linewidth]{surrogate2.png} 
\end{frame}

\begin{frame}[fragile]
\frametitle{AUTO\_INCREMENT}
Свойство атрибута (столбца) \emphb{AUTO\_INCREMENT} позволяет автоматически генерировать уникальное значение атрибута при вставке нового значения.
\begin{lstlisting}  
CREATE TABLE sales_slip (
  id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL, 
  sale_date DATE, 
  buyer_email VARCHAR(25) NOT NULL, 
FOREIGN KEY (buyer_email) REFERENCES buyers (email) 
  ON UPDATE CASCADE
  ON DELETE CASCADE
);
\end{lstlisting}  
\end{frame}

\begin{frame}[fragile]
\frametitle{Суррогатный ключ}
Добавим \emph{автоинкрементное} поле \emphb{ID}, а для обеспечения уникальности имени, фамилии и даты рождения используем \emphb{CONSTRAINT ... UNIQUE} 
\begin{lstlisting}
CREATE TABLE persons (
 id INTEGER NOT NULL AUTO_INCREMENT,
 fname VARCHAR(50) NOT NULL,
 lname VARCHAR(50) NOT NULL, 
 birthdate DATE NOT NULL, 
 address VARCHAR(100) NOT NULL,
CONSTRAINT PK_persons PRIMARY KEY PK_PERSONS (id),
CONSTRAINT UC_name_birth UNIQUE (fname, lname,birthdate)
);
\end{lstlisting}
\end{frame}

\begin{frame}[fragile]
\frametitle{Суррогатный ключ}
Таблица \emphb{payments} будет содержать числовое поле -- сcылку на \emphb{ID} из таблицы \emphb{persons}
\begin{lstlisting}
CREATE TABLE payments (
 payment_id INTEGER NOT NULL,
 payment_date DATE NOT NULL,
 payer_id INTEGER NOT NULL,    
CONSTRAINT PK_payments 
PRIMARY KEY PK_PAYMENTS (payment_id, payment_date),
CONSTRAINT FK_payments 
FOREIGN KEY (payer_id) REFERENCES persons (id)
);
\end{lstlisting}
\end{frame}
  
\begin{frame}[fragile]
\frametitle{Суррогатный ключ}
В СУБД есть специальный тип поля \emphb{SERIAL}, который является псеводимом записи \emphb{BIGINT UNSIGNED NOT NULL AUTO\_INCREMENT UNIQUE}
\begin{lstlisting}
CREATE TABLE persons (
 id SERIAL,
 fname VARCHAR(50) NOT NULL,
 lname VARCHAR(50) NOT NULL, 
 birthdate DATE NOT NULL, 
 address VARCHAR(100) NOT NULL,
CONSTRAINT PK_persons PRIMARY KEY PK_PERSONS (id),
CONSTRAINT UC_name_birth UNIQUE (fname, lname,birthdate)
);
\end{lstlisting} 
\end{frame}


\begin{frame}[fragile]
  \frametitle{Ограничения на значения столбцов}
  Для ограничения величины атрибутов используется ключевое слово \emphb{CHECK}.  
  \begin{lstlisting}  
  CREATE TABLE sales_slip (
    id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL, 
    sale_date DATE, 
    buyer_email VARCHAR(25) NOT NULL,
    sum_total NUMERIC(12,2) CHECK (sum_total>0)
  FOREIGN KEY (buyer_email) REFERENCES buyers (email)
  )
  \end{lstlisting}  
  Поле \emphb{sum\_total} должно быть больше нуля, но может быть и \emph{NULL}, т.к. нет требования \emphb{NOT NULL}. 
  \end{frame}


\begin{frame}[fragile]
\frametitle{Ограничения на значения столбцов}
\begin{lstlisting}  
CREATE TABLE students
(
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  birthdate DATE CHECK (birthdate > '1990-01-01'),
  enrollmentdate DATE,
  CONSTRAINT enrollment_gt CHECK (enrollmentdate > birthdate)
);
\end{lstlisting}  
\begin{lstlisting}  
INSERT INTO students (name, birthdate, enrollmentdate) 
VALUES ("Иночкин К. С.", "1991-12-25", "2015-08-01");
\end{lstlisting}  
\begin{lstlisting}  
INSERT INTO students (name, birthdate, enrollmentdate)
VALUES ("Митрофанова Е. В.", "1991-10-05", "1915-08-01");
\end{lstlisting}  
\end{frame}

\begin{frame}
\frametitle{Многие ко многим}
\includegraphics[width=1\linewidth]{Студент-Дисциплина.png}
\end{frame}


\begin{frame}
  \frametitle{Промежуточная сущность}
  \includegraphics[width=1\linewidth]{Многие_ко_многим_Чен.png}
\end{frame}

\begin{frame}
  \frametitle{Таблица связей n:m}
  \includegraphics[width=1\linewidth]{Многие_ко_многим_Таблица.png}
\end{frame}

\begin{frame}[fragile]
\frametitle{Отношение многие ко многим}
Студент = \emph{Идентификатор}, Фамилия, Имя, Отчество 
\begin{lstlisting}
CREATE TABLE students (
  id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
  fname VARCHAR(20),
  mname VARCHAR(20),
  lname VARCHAR(20)
);
\end{lstlisting}
\end{frame}

\begin{frame}[fragile]
\frametitle{Отношение многие ко многим}
Дисциплина = \emph{Идентификатор}, Наименование, Количество часов
\begin{lstlisting}
CREATE TABLE subjects (
  id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL, 
  name  VARCHAR(100), 
  hours INTEGER UNSIGNED NOT NULL
);
\end{lstlisting}
\end{frame}

\begin{frame}[fragile]
\frametitle{Отношение многие ко многим}
Таблица, связывающая дисциплину со студентом: 
\begin{lstlisting}
CREATE TABLE subject2student (
  student_id INTEGER NOT NULL, 
  subject_id INTEGER NOT NULL,
  mark       SMALLINT UNSIGNED,  
  mark_date  DATE,
FOREIGN KEY (student_id) REFERENCES students (id),
FOREIGN KEY (subject_id) REFERENCES subjects (id)
);
\end{lstlisting}
Пример (SQLite): \\
\tiny \url{https://colab.research.google.com/drive/1v913bNDvrWc0EeuwfIxwj-72Gk1FO4ik}
\end{frame}

\begin{frame}[fragile]
\frametitle{ON UPDATE, ON DELETE}
При помощи ключевых слов \emph{ON DELETE} и \emph{ON UPDATE} можно определить действия которые будут выполнятся в зависимой таблице (\emph{sales\_slip}) при изменении ключевого поля таблицы \emph{buyers}
\begin{verbatim}
FOREIGN KEY (имя_столбца,...) 
REFERENCES имя_таблицы (имя_столбца, ...) 
[ON DELETE действие]
[ON UPDATE действие]
\end{verbatim}
\end{frame}

\begin{frame}
\frametitle{ON UPDATE, ON DELETE}
\begin{tabular}{m{6em} m{20em}}  
  Действие & Описание \\
  \hline
  \emphb{CASCADE} & Изменение значения первичного ключа приводит к автоматическому изменению соответствующих значений внешнего ключа \\
  \hline
  \emphb{SET NULL} & При изменении значения или удалении первичного ключа все значения в связанных с внешним ключом колонках устанавливаются в NULL (появляются «брошенные» строки) \\
  \hline
  \emphb{RESTRICT} & Режим по умолчанию. Внешний ключ воспринимает только значения первичного ключа или NULL.  \\
  \hline
  \emphb{NO ACTION} & То же самое, что и RESTRICT
\end{tabular}
\end{frame}

\begin{frame}[fragile]
\frametitle{ON UPDATE, ON DELETE}
\begin{lstlisting}  
CREATE TABLE sales_slip (
  id INTEGER PRIMARY KEY NOT NULL, 
  sale_date DATE, 
  buyer_email VARCHAR(25) NOT NULL, 
FOREIGN KEY (buyer_email) REFERENCES buyers (email) 
  ON UPDATE CASCADE
  ON DELETE CASCADE
)
\end{lstlisting}
\begin{itemize}
  \item Изменение поля \emph{email} в таблице \emph{buyers} приведет к обновлению соответствующего значения  \emph{buyer\_email} 
  \item Удаление записи в таблице \emph{buyers} приведет к удалению всех записей с удаленным значением \emph{buyer\_email} 
\end{itemize}
\end{frame}


\section{Индексы}

\begin{frame}[fragile]
  \frametitle{Индекс}
  Ключевое поле в таблице \emph{buyers} имеет строковый тип \emph{VARCHAR}
  \begin{lstlisting}  
CREATE TABLE buyers (
  email VARCHAR(25) NOT NULL, 
  fname VARCHAR(25) NOT NULL, 
  lname VARCHAR(25) NOT NULL,  
PRIMARY KEY (email)
);
\end{lstlisting}    
\only<1>{
Если для поиска адреса эл. почты использовать обычный метод сравнения двух строк с последовательным просмотром всех строк таблицы, то это  приведет к большим затратам времени}
\only<2>{При создании таблицы \emph{buyers} будет автоматически создан \emph{индекс}: при вставке нового значения в таблицу строка (эл. адрес) будет дополнительно преобразована в цифровой код (\emph{хэш}), поиск заданной строки будет быстро выполнятся по её цифровому коду.}
\end{frame}

\begin{frame}
\frametitle{Индексы}
\begin{itemize}
\item Индексы в БД позволяют выполнять быстрый поиск данных 
\item В таблице автоматически создается индекс главного ключа и внешнего ключа
\item В таблице можно создавать пользовательские индексы
\end{itemize}  
\end{frame}


\begin{frame}[fragile]
\frametitle{CREATE INDEX}
Для быстрого поиска \emph{покупателя} с заданной фамилией в большой таблице, целесообразно объявить индекс для поля \emph{fname}:
\begin{lstlisting}  
CREATE INDEX fname_idx ON buyers(fname);
\end{lstlisting}    
Если требуется уникальность значений индексируемого поля, необходимо использовать ключевое слово \emph{UNIQUE}:
\begin{lstlisting}  
CREATE UNIQUE INDEX fname_idx ON buyers(fname);
\end{lstlisting}    
\end{frame}


\section{Изменение таблиц}

\begin{frame}[fragile]
\frametitle{Изменение таблиц}
Для изменения атрибутов и свойств таблицы используется инструкция \emphb{ALTER TABLE}, например для добавления столбца в таблицу:
\begin{lstlisting}  
ALTER TABLE ORDERS ADD COLUMN PRICE NUMERIC(9,2) 
\end{lstlisting}
Изменение типа столбца, если он уже есть, но другого типа
\begin{lstlisting}  
ALTER TABLE ORDERS ALTER COLUMN PRICE NUMERIC(9,2)
\end{lstlisting}
Удаление столбца
\begin{lstlisting}  
ALTER TABLE ORDERS DROP COLUMN PRICE
\end{lstlisting}  
{\scriptsize См. \url{https://www.w3schools.com/sql/sql_alter.asp}}
\imgsrc{Осипов Д. Л. Технологии проектирования баз данных. -- М.: ДМК Пресс, 2019.}
\end{frame}

\begin{frame}[fragile]
\frametitle{Изменение таблиц}
Добавление первичного ключа
\begin{lstlisting}  
ALTER TABLE ORDERS ADD PRIMARY KEY (ID);
\end{lstlisting}  
Добавление требования уникальности
\begin{lstlisting}  
ALTER TABLE Persons 
      ADD CONSTRAINT UC_Person 
      UNIQUE (ID, LastName);
\end{lstlisting}  
{\scriptsize См. \url{https://www.w3schools.com/sql/sql_alter.asp}}
\imgsrc{Осипов Д. Л. Технологии проектирования баз данных. -- М.: ДМК Пресс, 2019.}
\end{frame}


\begin{frame}[c]
\frametitle{Изменение таблиц}
Изменение таблиц может привести к \alert{потере существующих данных}, поэтому до всех изменений следует создать \alert{резервную копию} базы.  
\end{frame}

\begin{frame}[fragile]
\frametitle{Удаление таблицы}  
Удалить таблицу ORDERS
\begin{lstlisting}  
DROP TABLE ORDERS  
\end{lstlisting}
Удалить таблицу ORDERS, если она существует
\begin{lstlisting}  
DROP TABLE IF EXISTS ORDERS 
\end{lstlisting}
\end{frame}

\frame[plain]{
\frametitle{Список использованных источников}
\small
\begin{itemize}
  \item Хомоненко А. Д., Цыганков В. М., Мальцев М. Г. Базы данных: Учебник для высших учебных заведений / Под ред. npоф. А. Д. Хомоненко. -- 6-е изд., доп. - СПб.: КОРОНА-Век, 2009. -- 736 с.
  \item Осипов Д. Л. Технологии проектирования баз данных. -- М.: ДМК Пресс, 2019.  
  \item SQL Учебник \\
  {\scriptsize \url{https://schoolsw3.com/sql/index.php}}  
  \item SQL Tutorial \\
  {\scriptsize \url{https://www.tutorialspoint.com/sql/index.htm}}  
\end{itemize} 
}

\frame[plain,c]{
  \begin{center}
  \qrcode[height=1in]{https://classmech.ru/pages/databases/}\\
  \vspace{2mm}
  {\tiny \url{https://classmech.ru/pages/databases/} }
  \end{center}
}



\end{document}